#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
编码序列号持久化管理

负责设备编码序列号的数据库持久化操作：
- 序列号池状态持久化
- 灾难恢复支持
- 定时任务管理
"""

from datetime import datetime, date
from typing import Optional, Dict, Any, List

from common.database import DatabaseService
from common.exceptions import BizException, ErrorCode
from common.logger import create_logger

logger = create_logger(__name__)


class CodeSerialPersistence:
	"""编码序列号持久化管理"""

	@staticmethod
	async def get_pool_offset(db: DatabaseService, pool_name: str) -> Optional[Dict[str, Any]]:
		"""
		获取序列号池的偏移量信息

		Args:
			db: 数据库服务
			pool_name: 池名称

		Returns:
			池信息字典，不存在时返回None
		"""
		try:
			sql = """
                SELECT id, pool_name, pool_type, area_code, parent_code, date_str,
                       max_value, pool_size, last_reset_date, created_at, updated_at
                FROM dev_code_serial_offset
                WHERE pool_name = %s
            """
			result = db.get_one(sql, (pool_name,))

			if result:
				return {
					"id": result["id"],
					"pool_name": result["pool_name"],
					"pool_type": result["pool_type"],
					"area_code": result["area_code"],
					"parent_code": result["parent_code"],
					"date_str": result["date_str"],
					"max_value": result["max_value"],
					"pool_size": result["pool_size"],
					"last_reset_date": result["last_reset_date"],
					"created_at": result["created_at"],
					"updated_at": result["updated_at"]
				}
			return None

		except Exception as e:
			logger.exception(f"获取序列号池偏移量失败: pool_name={pool_name}")
			raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="获取序列号池偏移量失败") from e

	@staticmethod
	async def upsert_pool_offset(
			db: DatabaseService,
			pool_name: str,
			pool_type: str,
			max_value: int,
			pool_size: int,
			area_code: Optional[str] = None,
			parent_code: Optional[str] = None,
			date_str: Optional[str] = None
	) -> bool:
		"""
		插入或更新序列号池偏移量

		Args:
			db: 数据库服务
			pool_name: 池名称
			pool_type: 池类型
			max_value: 当前最大序列号值
			pool_size: 池容量上限
			area_code: 区域码（可选）
			parent_code: 父级设备编码（可选）
			date_str: 日期字符串（可选）

		Returns:
			是否成功
		"""
		try:
			sql = """
                INSERT INTO dev_code_serial_offset
                (pool_name, pool_type, area_code, parent_code, date_str, max_value, pool_size, created_at)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    max_value = VALUES(max_value),
                    pool_size = VALUES(pool_size),
                    updated_at = CURRENT_TIMESTAMP
            """

			rows_affected = db.update(sql, (
				pool_name, pool_type, area_code, parent_code, date_str,
				max_value, pool_size, datetime.now()
			))

			logger.info(f"更新序列号池偏移量: pool_name={pool_name}, max_value={max_value}")
			return rows_affected > 0

		except Exception as e:
			logger.exception(f"更新序列号池偏移量失败: pool_name={pool_name}")
			raise BizException.from_error_code(ErrorCode.INTERNAL_SERVER_ERROR, message="更新序列号池偏移量失败") from e

	@staticmethod
	async def get_pools_by_type(db: DatabaseService, pool_type: str, limit: int = 100) -> List[Dict[str, Any]]:
		"""
		根据池类型获取序列号池列表

		Args:
			db: 数据库服务
			pool_type: 池类型 (STATION/BOX/RTU/METER)
			limit: 返回数量限制

		Returns:
			池信息列表
		"""
		try:
			sql = """
                SELECT id, pool_name, pool_type, area_code, parent_code, date_str,
                       max_value, pool_size, last_reset_date, created_at, updated_at
                FROM dev_code_serial_offset
                WHERE pool_type = %s
                ORDER BY updated_at DESC
                LIMIT %s
            """
			results = db.get_all(sql, (pool_type, limit))

			pools = []
			for result in results:
				pools.append({
					"id": result["id"],
					"pool_name": result["pool_name"],
					"pool_type": result["pool_type"],
					"area_code": result["area_code"],
					"parent_code": result["parent_code"],
					"date_str": result["date_str"],
					"max_value": result["max_value"],
					"pool_size": result["pool_size"],
					"last_reset_date": result["last_reset_date"],
					"usage_percent": round((result["max_value"] / result["pool_size"]) * 100, 2) if result[
																										"pool_size"] > 0 else 0,
					"created_at": result["created_at"],
					"updated_at": result["updated_at"]
				})

			return pools

		except Exception as e:
			logger.exception(f"获取序列号池列表失败: pool_type={pool_type}")
			raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="获取序列号池列表失败") from e

	@staticmethod
	async def get_daily_station_pools(db: DatabaseService, date_str: Optional[str] = None) -> List[Dict[str, Any]]:
		"""
		获取指定日期的台区序列号池

		Args:
			db: 数据库服务
			date_str: 日期字符串（YYYYMMDD），默认为今天

		Returns:
			台区池列表
		"""
		if not date_str:
			date_str = date.today().strftime("%Y%m%d")

		try:
			sql = """
                SELECT id, pool_name, area_code, max_value, pool_size,
                       last_reset_date, created_at, updated_at
                FROM dev_code_serial_offset
                WHERE pool_type = 'STATION' AND date_str = %s
                ORDER BY area_code
            """
			results = db.get_all(sql, (date_str,))

			pools = []
			for result in results:
				pools.append({
					"id": result["id"],
					"pool_name": result["pool_name"],
					"area_code": result["area_code"],
					"max_value": result["max_value"],
					"pool_size": result["pool_size"],
					"usage_percent": round((result["max_value"] / result["pool_size"]) * 100, 2) if result[
																										"pool_size"] > 0 else 0,
					"last_reset_date": result["last_reset_date"],
					"created_at": result["created_at"],
					"updated_at": result["updated_at"]
				})

			return pools

		except Exception as e:
			logger.exception(f"获取日期台区池失败: date_str={date_str}")
			raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="获取台区序列号池失败") from e

	@staticmethod
	async def reset_daily_pools(db: DatabaseService, area_codes: List[str], date_str: Optional[str] = None) -> Dict[
		str, bool]:
		"""
		重置指定区域的日级序列号池

		Args:
			db: 数据库服务
			area_codes: 区域码列表
			date_str: 日期字符串，默认为今天

		Returns:
			重置结果 {area_code: success}
		"""
		if not date_str:
			date_str = date.today().strftime("%Y%m%d")

		results = {}

		async with db.transaction():
			for area_code in area_codes:
				try:
					pool_name = f"station:{area_code}:{date_str}"

					# 重置或创建池记录
					success = await CodeSerialPersistence.upsert_pool_offset(
						db=db,
						pool_name=pool_name,
						pool_type="STATION",
						max_value=0,
						pool_size=100000,  # 默认10万容量
						area_code=area_code,
						date_str=date_str
					)

					if success:
						# 更新重置日期
						update_sql = """
                            UPDATE dev_code_serial_offset
                            SET last_reset_date = %s
                            WHERE pool_name = %s
                        """
						db.update(update_sql, (date.today(), pool_name))

					results[area_code] = success
					logger.info(f"重置台区序列号池: {pool_name}, success={success}")

				except Exception as e:
					logger.error(f"重置台区序列号池失败: area_code={area_code}, error={e}")
					results[area_code] = False

		return results

	@staticmethod
	async def get_pool_statistics(db: DatabaseService) -> Dict[str, Any]:
		"""
		获取序列号池统计信息

		Args:
			db: 数据库服务

		Returns:
			统计信息字典
		"""
		try:
			# 按类型统计
			type_stats_sql = """
                SELECT
                    pool_type,
                    COUNT(*) as pool_count,
                    AVG(max_value / pool_size * 100) as avg_usage_percent,
                    MAX(max_value / pool_size * 100) as max_usage_percent,
                    SUM(CASE WHEN max_value / pool_size >= 0.9 THEN 1 ELSE 0 END) as high_usage_count
                FROM dev_code_serial_offset
                WHERE pool_size > 0
                GROUP BY pool_type
            """
			type_results = db.get_all(type_stats_sql)

			# 总体统计
			total_stats_sql = """
                SELECT
                    COUNT(*) as total_pools,
                    COUNT(DISTINCT area_code) as area_count,
                    SUM(CASE WHEN max_value / pool_size >= 0.9 THEN 1 ELSE 0 END) as total_warning_pools,
                    AVG(max_value / pool_size * 100) as overall_avg_usage
                FROM dev_code_serial_offset
                WHERE pool_size > 0
            """
			total_result = db.get_one(total_stats_sql)

			# 今日台区池统计
			today = date.today().strftime("%Y%m%d")
			daily_stats_sql = """
                SELECT
                    COUNT(*) as today_station_pools,
                    AVG(max_value / pool_size * 100) as today_avg_usage,
                    MAX(max_value) as today_max_sequence
                FROM dev_code_serial_offset
                WHERE pool_type = 'STATION' AND date_str = %s AND pool_size > 0
            """
			daily_result = db.get_one(daily_stats_sql, (today,))

			return {
				"by_type": [dict(row) for row in type_results],
				"total": dict(total_result) if total_result else {},
				"today": dict(daily_result) if daily_result else {},
				"generated_at": datetime.now().isoformat()
			}

		except Exception as e:
			logger.exception("获取序列号池统计信息失败")
			raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="获取序列号池统计信息失败") from e

	@staticmethod
	async def cleanup_expired_pools(db: DatabaseService, days_old: int = 30) -> int:
		"""
		清理过期的序列号池记录

		Args:
			db: 数据库服务
			days_old: 保留天数

		Returns:
			清理的记录数
		"""
		try:
			# 只清理日级台区池（其他池持久保留）
			sql = """
                DELETE FROM dev_code_serial_offset
                WHERE pool_type = 'STATION'
                  AND date_str IS NOT NULL
                  AND STR_TO_DATE(date_str, '%Y%m%d') < DATE_SUB(CURDATE(), INTERVAL %s DAY)
            """

			rows_deleted = db.delete(sql, (days_old,))
			logger.info(f"清理过期序列号池记录: {rows_deleted}条, 保留天数: {days_old}")

			return rows_deleted

		except Exception as e:
			logger.exception(f"清理过期序列号池记录失败: days_old={days_old}")
			raise BizException.from_error_code(ErrorCode.INTERNAL_SERVER_ERROR,
											   message="清理过期序列号池记录失败") from e


class ScheduledTaskManager:
	"""定时任务管理"""

	@staticmethod
	async def get_task_by_name(db: DatabaseService, task_name: str) -> Optional[Dict[str, Any]]:
		"""
		根据任务名称获取定时任务

		Args:
			db: 数据库服务
			task_name: 任务名称

		Returns:
			任务信息字典
		"""
		try:
			sql = """
                SELECT id, task_name, task_type, cron_expression, task_params,
                       enabled, last_run_time, next_run_time, run_count, fail_count,
                       status, error_msg, created_at, updated_at
                FROM sys_scheduled_task
                WHERE task_name = %s
            """
			result = db.get_one(sql, (task_name,))

			if result:
				return dict(result)
			return None

		except Exception as e:
			logger.exception(f"获取定时任务失败: task_name={task_name}")
			raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="获取定时任务失败") from e

	@staticmethod
	async def update_task_run_status(
			db: DatabaseService,
			task_name: str,
			status: str,
			error_msg: Optional[str] = None,
			next_run_time: Optional[datetime] = None
	) -> bool:
		"""
		更新任务运行状态

		Args:
			db: 数据库服务
			task_name: 任务名称
			status: 运行状态
			error_msg: 错误信息（可选）
			next_run_time: 下次运行时间（可选）

		Returns:
			是否成功
		"""
		try:
			# 增加运行次数，如果失败则增加失败次数
			if status == "FAILED":
				sql = """
                    UPDATE sys_scheduled_task
                    SET status = %s, error_msg = %s, last_run_time = %s,
                        next_run_time = %s, run_count = run_count + 1,
                        fail_count = fail_count + 1, updated_at = %s
                    WHERE task_name = %s
                """
			else:
				sql = """
                    UPDATE sys_scheduled_task
                    SET status = %s, error_msg = %s, last_run_time = %s,
                        next_run_time = %s, run_count = run_count + 1, updated_at = %s
                    WHERE task_name = %s
                """

			now = datetime.now()
			rows_affected = db.update(sql, (
				status, error_msg, now, next_run_time, now, task_name
			))

			return rows_affected > 0

		except Exception as e:
			logger.exception(f"更新任务运行状态失败: task_name={task_name}")
			raise BizException.from_error_code(ErrorCode.INTERNAL_SERVER_ERROR, message="更新任务运行状态失败") from e

	@staticmethod
	async def get_enabled_tasks(db: DatabaseService) -> List[Dict[str, Any]]:
		"""
		获取所有启用的定时任务

		Args:
			db: 数据库服务

		Returns:
			任务列表
		"""
		try:
			sql = """
                SELECT id, task_name, task_type, cron_expression, task_params,
                       enabled, last_run_time, next_run_time, run_count, fail_count,
                       status, error_msg, created_at, updated_at
                FROM sys_scheduled_task
                WHERE enabled = 1
                ORDER BY next_run_time ASC
            """
			results = db.get_all(sql)

			return [dict(row) for row in results]

		except Exception as e:
			logger.exception("获取启用定时任务失败")
			raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="获取定时任务失败") from e
